import pandas as pd
import numpy as np

# 交叉表：一列数据对另一列数据分类的数量影响（eg.某天是星期几对这天股票是涨1还是跌0的数量影响，即星期i 有几次是涨有几次是跌）
# pd.crosstab()
# 透视表：一列数据对另一列数据分类的占比影响（eg.某天是星期几对这天股票是涨1还是跌0的占比影响，即星期i 涨占比和跌占比）
# data.pivot_table()
data = pd.read_csv("stock_day.csv").drop(["ma5", "ma10", "ma20", "v_ma5", "v_ma10", "v_ma20"], axis=1)
data.index
# 将索引转换为时间格式
time = pd.to_datetime(data.index)

# 获取每个索引对应的星期几 time.weekday
"""
Int64Index([1, 0, 4, 3, 2, 1, 0, 4, 3, 2,
            ...
            4, 3, 2, 1, 0, 4, 3, 2, 1, 0],
           dtype='int64', length=643)
"""
time.weekday

# 获取每个索引对应几号 time.day
"""
Int64Index([27, 26, 23, 22, 14, 13, 12, 9, 8, 7,
            ...
            13, 12, 11, 10, 9, 6, 5, 4, 3, 2],
           dtype='int64', length=643)
"""

data["weekday"] = time.weekday
# 1代表涨，0代表跌
data["p_n"] = np.where(data["p_change"] > 0, 1, 0)
print(data)
"""
             open   high  close    low  ...  p_change  turnover  weekday  p_n
2018-02-27  23.53  25.88  24.16  23.53  ...      2.68      2.39        1    1
2018-02-26  22.80  23.78  23.53  22.80  ...      3.02      1.53        0    1
2018-02-23  22.88  23.37  22.82  22.71  ...      2.42      1.32        4    1
2018-02-22  22.25  22.76  22.28  22.02  ...      1.64      0.90        3    1
2018-02-14  21.49  21.99  21.92  21.48  ...      2.05      0.58        2    1
...           ...    ...    ...    ...  ...       ...       ...      ...  ...
2015-03-06  13.17  14.48  14.28  13.13  ...      8.51      6.16        4    1
2015-03-05  12.88  13.45  13.16  12.87  ...      2.02      3.19        3    1
2015-03-04  12.80  12.92  12.90  12.61  ...      1.57      2.30        2    1
2015-03-03  12.52  13.06  12.70  12.52  ...      1.44      4.76        1    1
2015-03-02  12.25  12.67  12.52  12.20  ...      2.62      3.30        0    1
"""

# 交叉表
count = pd.crosstab(index=data["weekday"], columns=data["p_n"])
print(count)
"""
p_n       0   1
weekday        
0        63  62
1        55  76
2        61  71
3        63  65
4        59  68
"""

# 透视表，仅统计 data为1 的占比
pcount = data.pivot_table(["p_n"], index="weekday")
print(pcount)
"""
              p_n
weekday          
0        0.496000
1        0.580153
2        0.537879
3        0.507812
4        0.535433
"""